package com.starbaba.stat.service.impl;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatCoreBehaviorModel;
import com.starbaba.stat.model.StatNewUserModel;
import com.starbaba.stat.service.StatCoreBehaviorDayService;
import com.starbaba.stat.utils.Constant;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@SuppressWarnings("rawtypes")
@Component
public class StatCoreBehaviorDayServiceImpl implements StatCoreBehaviorDayService{

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		
		List<StatNewUserModel> prdList = dao.list("select distinct a.prd_id, b.name prd_name from stat.stat_loan_core_behavior a, loan.comm_product_prdid b where a.prd_id = b.prdid ", StatNewUserModel.class);
		List<StatNewUserModel> channelList = dao.list("select distinct a.channel, b.channel_name channel_name from stat.stat_loan_core_behavior a, loan.comm_product_channel b where a.channel = b.channel_code", StatNewUserModel.class);
		
		StatNewUserModel all = new StatNewUserModel();
		all.setPrd_id("all");
		all.setChannel("all");
		all.setPrd_name("全部");
		all.setChannel_name("全部");
		
		prdList.add(0, all);
		channelList.add(0, all);
		
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
	}

	@Override
	public List getListDay(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel"); 
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and date >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and date <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform) && !"all".equals(platform)) {
			whereParams.append("and platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id) && !"all".equals(prd_id)) {
			whereParams.append("and prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"all".equals(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		
		StringBuilder groupBy = new StringBuilder();
		groupBy.append("group by date, ");
		if (!"all".equals(platform)) {
			groupBy.append("platform, ");
		}
		if (!"all".equals(prd_id)) {
			groupBy.append("prd_id, ");
		}
		if (!"all".equals(channel)) {
			groupBy.append("channel, ");
		}
		String groupByString = groupBy.toString().substring(0, groupBy.length() - 2) + " ";
		
		StringBuilder listSql = new StringBuilder("SELECT date, platform, name prdId, channel_code, channel_name channel, sum(increased) increased, sum(new_registered) newRegisterde, ");
		listSql.append("sum(new_apply_count) newApplyCount, sum(new_loan_success) newLoanSuccess, sum(new_success_settle) newSuccessSettle, ");
		listSql.append("sum(loan_loan_success) LoanLoanSuccess, sum(loan_success_settle) LoanSuccessSettle from ");
		listSql.append("stat.stat_loan_core_behavior, loan.comm_product_prdid a, loan.comm_product_channel ");
		listSql.append("WHERE prd_id = a.prdid AND channel = channel_code ");
		listSql.append(whereParams).append(groupByString);
		listSql.append("order by date desc ");
		
		String sqlCount = "select count(1) from (" + listSql.toString() + ") z ";
		if (start != null && length != null) {
			listSql.append("limit " + start + ", " + length);
		}
		
		int count = dao.getInt(sqlCount);
		List<StatCoreBehaviorModel> list = dao.list(listSql.toString(), StatCoreBehaviorModel.class);
		for (StatCoreBehaviorModel model : list) {
			if (StringUtils.isEmpty(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (StringUtils.isEmpty(model.getPrdId())) {
				model.setPrdId("未知");
			}
			if (StringUtils.isEmpty(model.getChannel())) {
				model.setChannel(model.getChannel_code());
			}
			
			if ("all".equals(platform)) {
				model.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				model.setPrdId("全部");
			}
			if ("all".equals(channel)) {
				model.setChannel("全部");
			}
			
			if (!"0".equals(model.getIncreased()) && StringUtils.isNotEmpty(model.getIncreased())) {
				model.setNewRegisterdeRate(getPercent(model.getNewRegisterde(), model.getIncreased()));
				model.setNewLoanSuccessRate(getPercent(model.getNewLoanSuccess(), model.getIncreased()));
			} else {
				model.setNewRegisterdeRate("0");
				model.setNewLoanSuccessRate("0");
			}
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}
	
	public List<StatCoreBehaviorModel> getExcelListDay(Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel"); 
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and date >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and date <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform) && !"all".equals(platform)) {
			whereParams.append("and platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id) && !"all".equals(prd_id)) {
			whereParams.append("and prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"all".equals(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		
		StringBuilder groupBy = new StringBuilder();
		groupBy.append("group by date, ");
		if (!"all".equals(platform)) {
			groupBy.append("platform, ");
		}
		if (!"all".equals(prd_id)) {
			groupBy.append("prd_id, ");
		}
		if (!"all".equals(channel)) {
			groupBy.append("channel, ");
		}
		String groupByString = groupBy.toString().substring(0, groupBy.length() - 2) + " ";
		
		StringBuilder listSql = new StringBuilder("SELECT date, platform, name prdId, channel_code, channel_name channel, sum(increased) increased, sum(new_registered) newRegisterde, ");
		listSql.append("sum(new_apply_count) newApplyCount, sum(new_loan_success) newLoanSuccess, sum(new_success_settle) newSuccessSettle, ");
		listSql.append("sum(loan_loan_success) LoanLoanSuccess, sum(loan_success_settle) LoanSuccessSettle from ");
		listSql.append("stat.stat_loan_core_behavior, loan.comm_product_prdid a, loan.comm_product_channel ");
		listSql.append("WHERE prd_id = a.prdid AND channel = channel_code ");
		listSql.append(whereParams).append(groupByString);
		listSql.append("order by date desc ");
		
		List<StatCoreBehaviorModel> list = dao.list(listSql.toString(), StatCoreBehaviorModel.class);
		for (StatCoreBehaviorModel model : list) {
			if (StringUtils.isEmpty(model.getPlatform())) {
				model.setPlatform("未知");
			}
			if (StringUtils.isEmpty(model.getPrdId())) {
				model.setPrdId("未知");
			}
			if (StringUtils.isEmpty(model.getChannel())) {
				model.setChannel(model.getChannel_code());
			}
			
			if ("all".equals(platform)) {
				model.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				model.setPrdId("全部");
			}
			if ("all".equals(channel)) {
				model.setChannel("全部");
			}
			
			if (!"0".equals(model.getIncreased()) && StringUtils.isNotEmpty(model.getIncreased())) {
				model.setNewRegisterdeRate(getPercent(model.getNewRegisterde(), model.getIncreased()));
				model.setNewLoanSuccessRate(getPercent(model.getNewLoanSuccess(), model.getIncreased()));
			} else {
				model.setNewRegisterdeRate("0");
				model.setNewLoanSuccessRate("0");
			}
		}
		return list;
	}
	
	
	
	@Override
	public List getListWeek(Integer start, Integer length,
			PageResponse pageResponse, Map<String, Object> paramsMap)
			throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List getListMonth(Integer start, Integer length,
			PageResponse pageResponse, Map<String, Object> paramsMap)
			throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	public static String getPercent(Long a, Long b) {
		return (float) Math.round((float) a / b * 10000) / 100 + "%";
	}
	
	public static String getPercent(String a, String b) {
		if (StringUtils.isEmpty(a) || StringUtils.isEmpty(b)){
			return "0";
		}
		Long al = Long.parseLong(a);
		Long bl = Long.parseLong(b);

		return (float) Math.round((float) al / bl * 10000) / 100 + "%";
	}
	
}
